package com.cse587.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Component;
import org.springframework.transaction.support.TransactionTemplate;

import com.cse587.entities.Country;
import com.cse587.entities.CountryBorder;
import com.cse587.entities.StatisticsInfo;

@Component
public class CountryDao {
	// private static final String INSERT_COUNTRY =
	// "insert into country(id, full_name, iso3_code) values(:id, :fullName, :codeISO3)";
	// INSERT && UPDATE SQL
	private static final String INSERT_COUNTRY = "INSERT INTO country(full_name, iso3_code) VALUES( ?, ?)";
	private static final String UPDATE_COUNTRY = "UPDATE country SET max_lat = ?, max_long = ?, min_lat = ?, min_long = ?,capital = ? WHERE id =?";
	private static final String UPDATE_COUNTRY_BY_ISOCODE = "UPDATE country SET max_lat = ?, max_long = ?, min_lat = ?, min_long = ?, capital = ?, capital_lat = ?, capital_lng = ? WHERE iso3_code =?";
	private static final String UPDATE_COUNTRY_CAPITAL_LAT_LONG = "UPDATE country SET capital_lat = ?, capital_lng = ? WHERE iso3_code =?";
	private static final String INSERT_COUNTRY_STATISTICS = "INSERT INTO statisticsinfo(cid, year, property_name, property_value) VALUES( ?, ?, ?, ?)";
	private static final String INSERT_COUNTRY_BORDER = "INSERT INTO border(cid1, cid2) VALUES(?, ?)";
	// private static final String UPDATE_COUNTRY_CAPITAL =
	// "UPDATE country SET capital = ? WHERE iso3_code =?";
	private static final String UPDATE_COUNTRY_UNCODE = "UPDATE country SET un_code = ? WHERE iso3_code =?";
	// QUERY SQL
	private static final String SELECT_COUNTRY = "SELECT * FROM country";
	private static final String SELECT_COUNTRY_BORDER = "SELECT * FROM border WHERE cid1 = ?";
	private static final String SELECT_COUNTRY_STAT_INFO = "SELECT * FROM statisticsinfo WHERE cid = ?";
	private static final String SELECT_COUNTRY_POPULATION = "SELECT * FROM statisticsinfo WHERE property_name = 'Population'";
	private static final String SELECT_COUNTRY_ALTERNATIVE_NAME = "SELECT * FROM  alternative_country_name WHERE alpha3 = ?";

	private JdbcTemplate jdbcTemplate;
	private TransactionTemplate transactionTemplate;

	private class CountryMapper implements RowMapper<Country> {
		public Country mapRow(ResultSet rs, int rowNum) throws SQLException {
			Country country = new Country();
			country.setId(rs.getInt("id"));
			country.setCodeISO3(rs.getString("iso3_code"));
			country.setFullName(rs.getString("full_name"));
			country.setMaxLat(rs.getDouble("max_lat"));
			country.setMaxLong(rs.getDouble("max_long"));
			country.setMinLat(rs.getDouble("min_lat"));
			country.setMinLong(rs.getDouble("min_long"));
			country.setCapital(rs.getString("capital"));
			country.setUnCode(rs.getInt("un_code"));
			country.setCapitalLat(rs.getDouble("capital_lat"));
			country.setCapitalLong(rs.getDouble("capital_lng"));
			return country;
		}
	}

	private class StatisticsInfoMapper implements RowMapper<StatisticsInfo> {
		public StatisticsInfo mapRow(ResultSet rs, int rowNum)
				throws SQLException {
			StatisticsInfo info = new StatisticsInfo();
			info.setId(rs.getInt("id"));
			info.setPropertyName(rs.getString("property_name"));
			info.setPropertyValue(rs.getString("property_value"));
			info.setYear(rs.getString("year"));
			info.setCid(rs.getInt("cid"));
			return info;
		}
	}

	private class BoderMapper implements RowMapper<CountryBorder> {
		public CountryBorder mapRow(ResultSet rs, int rowNum)
				throws SQLException {
			CountryBorder border = new CountryBorder();
			border.setId(rs.getInt("id"));
			border.setCid1(rs.getString("cid1"));
			border.setCid2(rs.getString("cid2"));

			return border;
		}
	}

	private class CountryAlternativeNameMapper implements RowMapper<String> {
		public String mapRow(ResultSet rs, int rowNum) throws SQLException {
			if (rs.getString("alter_name").length() > 0) {
				return rs.getString("alter_name");
			} else {
				return "";
			}
		}
	}

	@Resource
	public void setDataSource(DataSource dataSource) {
		jdbcTemplate = new JdbcTemplate(dataSource);
	}

	// @Resource
	// public void setDefaultTransactionManager(
	// PlatformTransactionManager defaultTransactionManager) {
	// transactionTemplate = new TransactionTemplate(defaultTransactionManager);
	// }

	public int createObject(final Country c) {
		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplate.update(new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(Connection conn)
					throws SQLException {
				PreparedStatement ps = conn.prepareStatement(INSERT_COUNTRY,
						new String[] { "id" });
				ps.setString(1, c.getFullName());
				ps.setString(2, c.getCodeISO3());
				return ps;
			}
		}, keyHolder);
		return keyHolder.getKey().intValue();
	}

	public void updateCountry(final Country c) {
		// use BeanPropertySqlParameterSource to map the Object to spl parm
		jdbcTemplate.update(
				UPDATE_COUNTRY,
				new Object[] { c.getMaxLat(), c.getMaxLong(), c.getMinLat(),
						c.getMinLong(), c.getCapital(), c.getId() });
	}

	public void updateCountryUncode(final Country c) {
		// use BeanPropertySqlParameterSource to map the Object to spl parm
		jdbcTemplate.update(UPDATE_COUNTRY_UNCODE, new Object[] {
				c.getUnCode(), c.getCodeISO3() });
	}

	public void updateCapitalLatAndLong(double lat, double lng, String iso3Code) {
		// use BeanPropertySqlParameterSource to map the Object to spl parm
		jdbcTemplate.update(UPDATE_COUNTRY_CAPITAL_LAT_LONG, new Object[] {
				lat, lng, iso3Code });
	}

	public void batchUpdateCountry(Map<String, Country> map) {
		List<Object[]> batch = new ArrayList<Object[]>();
		for (Map.Entry<String, Country> c : map.entrySet()) {
			Object[] values = new Object[] { c.getValue().getMaxLat(),
					c.getValue().getMaxLong(), c.getValue().getMinLat(),
					c.getValue().getMinLong(), c.getValue().getCapital(),
					c.getValue().getCapitalLat(),
					c.getValue().getCapitalLong(), c.getKey() };
			batch.add(values);
		}
		jdbcTemplate.batchUpdate(UPDATE_COUNTRY_BY_ISOCODE, batch);
	}

	public void addCountryInfo(int cid, String propertyName,
			String propertyValue, String year) {
		jdbcTemplate.update(INSERT_COUNTRY_STATISTICS, new Object[] { cid,
				propertyName, propertyValue, year });
	}

	public void addBatchBorder(List<CountryBorder> borderList) {
		List<Object[]> batch = new ArrayList<Object[]>();
		for (CountryBorder border : borderList) {
			Object[] values = new Object[] { border.getCid1(), border.getCid2() };
			batch.add(values);
		}
		jdbcTemplate.batchUpdate(INSERT_COUNTRY_BORDER, batch);
	}

	// ////////////////////////////fetch function//////////////////////////

	public List<Country> getALLCountry() {
		List<Country> countryList = jdbcTemplate.query(SELECT_COUNTRY,
				new CountryMapper());
		return countryList;
	}

	public List<StatisticsInfo> getCountryStatInfo(int cid) {
		List<StatisticsInfo> result = jdbcTemplate.query(
				SELECT_COUNTRY_STAT_INFO, new Object[] { cid },
				new StatisticsInfoMapper());
		return result;
	}
	
	public List<StatisticsInfo> getAllPopulationValue() {
		List<StatisticsInfo> result = jdbcTemplate.query(
				SELECT_COUNTRY_POPULATION,
				new StatisticsInfoMapper());
		return result;
	}

	public List<CountryBorder> getCountryBorder(String iso3Code) {
		List<CountryBorder> result = jdbcTemplate.query(SELECT_COUNTRY_BORDER,
				new Object[] { iso3Code }, new BoderMapper());
		return result;
	}

	public String getCountryAlternativename(String iso3Code) {
		List<String> result = jdbcTemplate.query(
				SELECT_COUNTRY_ALTERNATIVE_NAME, new Object[] { iso3Code },
				new CountryAlternativeNameMapper());
		return result.isEmpty() ? null : result.get(0);
	}
}
